package com.pointi.PatternExtractor;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.pointi.emoTools.*;

public class JDBC extends emoJDBC
{
	//____________________________________________________________________________________________________
	// RANGE LIMIT

	public void fnGetAllDataRange(emoPattern range, emoParam param, String date)
	{
		// PREPARE
		String t_bd_full_log = "t_bd_full_log_" + date ;
		// QUERY
		String query = "" ;
		query = "SELECT " ;
		for(int i=0; i<param.length(); i++)
		{
			if(i != 0) query += ", " ;
			query += String.format("MIN(FL.%s) AS '%s_min', MAX(FL.%s) AS '%s_max' ", param.param(i), param.param(i), param.param(i), param.param(i), param.param(i), param.param(i)) ;
		}
		query += "FROM "+ t_bd_full_log +" AS FL ;" ; 
		this.out(query) ;
		// RESULT-SET
		Statement mST = null ;
		ResultSet mRS = null ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ;
			while (mRS.next()) {
				for(int i=0; i<param.length(); i++)
				{
					range.min(i, mRS.getDouble(param.param(i) + "_min")) ;
					range.max(i, mRS.getDouble(param.param(i) + "_max")) ;
				}
			}
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}

	//____________________________________________________________________________________________________
	// INITIAL RANGE

	public void fnGetIntersectionRange(double ratio, emoPattern range, emoParam param, String date)
	{
		// PREPARE
		String t_bd_full_log = "t_bd_full_log_" + date ;
		// QUERY
		String query = "" ;
		query = "SELECT " ;
		for(int i=0; i<param.length(); i++)
		{
			if(i != 0) query += ", " ;
			query += String.format("MIN(IL.%s) AS '%s_min', MAX(IL.%s) AS '%s_max' ", param.param(i), param.param(i), param.param(i), param.param(i), param.param(i), param.param(i)) ;
		}
		query += "FROM (SELECT DUR.callkey AS 'callkey'" ;
		for(int i=0; i<param.length(); i++)
		{
			query += String.format(", %s.seq AS '%s_seq', %s.val AS '%s'", param.param(i), param.param(i), param.param(i), param.param(i)) ;
		}
		query += "FROM " ;
		for(int i=0; i<param.length(); i++)
		{
			query += "(SELECT F.callkey, F.val, F.[avg], F.rank, ROW_NUMBER() OVER(ORDER BY rank, callkey) AS 'seq' " ; 
			query += String.format("FROM (SELECT F1.callkey, F1.%s AS 'val', F2.[avg], RANK() OVER(ORDER BY ABS(F1.%s - F2.[avg])) AS 'rank' ", param.param(i), param.param(i)) ;
			query += String.format("FROM "+ t_bd_full_log +" AS F1, (SELECT AVG(F.%s) AS 'avg' FROM "+ t_bd_full_log +" AS F) AS F2) F) AS %s ", param.param(i), param.param(i)) ;
			if(i != 0) query += String.format("ON %s.callkey = %s.callkey ", param.param(i), param.param((i-1))) ;
			if((i+1) != param.length()) query += "FULL OUTER JOIN " ;
		}
		query += ", (SELECT CEILING((COUNT(*) * "+ (double)(ratio/100.0) +")) AS 'cnt' FROM "+ t_bd_full_log +") AS CNT " ;
		query += "WHERE " ; 
		for(int i=0; i<param.length(); i++)
		{
			if(i != 0) query += "AND " ;
			query += String.format("%s.seq <= CNT.cnt ", param.param(i)) ; 
		}
		query += ") IL ; " ;
		this.out(query) ;
		// RESULT-SET
		Statement mST = null ;
		ResultSet mRS = null ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ;
			while (mRS.next()) {
				for(int i=0; i<param.length(); i++)
				{
					range.min(i, mRS.getDouble(param.param(i) + "_min")) ;
					range.max(i, mRS.getDouble(param.param(i) + "_max")) ;
				}
			}
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}
	
	//____________________________________________________________________________________________________
	// INDIVISUAL RANGE
	
	public void fnGetIndivisualRange(int targetCount, emoPattern range, emoParam param, String date)
	{
		// PREPARE
		String t_bd_full_log = "t_bd_full_log_" + date ;
		// QUERY
		String query = "" ;
		query = "SELECT " ;
		for(int i=0; i<param.length(); i++)
		{
			if(i != 0) query += ", " ;
			query += String.format("MIN(IL.%s) AS '%s_min', MAX(IL.%s) AS '%s_max' ", param.param(i), param.param(i), param.param(i), param.param(i), param.param(i), param.param(i)) ;
		}
		query += "FROM (SELECT " ;
		for(int i=0; i<param.length(); i++)
		{
			query += String.format("%s.val AS '%s' ", param.param(i), param.param(i)) ;
			if((i+1) != param.length()) query += ", " ;
		}
		query += "FROM " ;
		for(int i=0; i<param.length(); i++)
		{
			query += "(SELECT F.val, F.rank, ROW_NUMBER() OVER(ORDER BY F.rank, F.val) AS 'seq' FROM " ;
			query += String.format("(SELECT F1.%s AS 'val', RANK() OVER(ORDER BY ABS(F1.%s - F2.[avg])) AS 'rank' FROM ", param.param(i), param.param(i)) ;
			query += t_bd_full_log ;
			query += String.format(" AS F1, (SELECT AVG(F.%s) AS 'avg' FROM t_bd_full_log_201406 AS F) AS F2) F) AS %s ", param.param(i), param.param(i)) ;
			if(i != 0) query += String.format("ON %s.seq = %s.seq ", param.param(i-1), param.param(i)) ;
			if((i+1) != param.length()) query += "INNER JOIN " ;
		}
		query += String.format("WHERE %s.seq <= %d ) AS IL", param.param(0), targetCount) ;
		this.out(query) ;
		// RESULT-SET
		Statement mST = null ;
		ResultSet mRS = null ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ;
			while (mRS.next()) {
				for(int i=0; i<param.length(); i++)
				{
					range.min(i, mRS.getDouble(param.param(i) + "_min")) ;
					range.max(i, mRS.getDouble(param.param(i) + "_max")) ;
				}
			}
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
	}

	//____________________________________________________________________________________________________
	// EXPAND // Range Minimum

	public static int TYPE_IN_OUT = 1 ;
	public static int TYPE_OUT_IN = 2 ;
	
	public double fnNextRangeMin(String param, double min, String date, int type)
	{
		double next_val = -1.0 ;
		// PREPARE
		String t_bd_full_log = "t_bd_full_log_" + date ;
		// QUERY
		String query = "" ;
		if(type == JDBC.TYPE_OUT_IN)
		{
			query = " SELECT TOP 1 F." + param + 
					" FROM " + t_bd_full_log + " AS F " +
					" WHERE F." + param + " > " + min +
					" ORDER BY F." + param + " ASC ; " ;		
		} else {
			query = " SELECT TOP 1 F." + param + 
					" FROM " + t_bd_full_log + " AS F " +
					" WHERE F." + param + " < " + min +
					" ORDER BY F." + param + " DESC ; " ;		
		}
		this.out(query) ;
		// RESULT-SET
		Statement mST = null ;
		ResultSet mRS = null ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ;
			while (mRS.next()) {
				next_val = mRS.getDouble(param) ;
			}
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		return next_val ;
	}

	//____________________________________________________________________________________________________
	// EXPAND // Range Maximum

	public double fnNextRangeMax(String param, double max, String date, int type)
	{
		double next_val = -1.0 ;
		// PREPARE
		String t_bd_full_log = "t_bd_full_log_" + date ;
		// QUERY
		String query = "" ;
		
		if(type == JDBC.TYPE_OUT_IN) {
			query = " SELECT TOP 1 F." + param +
					" FROM " + t_bd_full_log + " AS F " +
					" WHERE F." + param + " < " + max +
					" ORDER BY F." + param + " DESC ; " ;
		} else {
			query = " SELECT TOP 1 F." + param +
					" FROM " + t_bd_full_log + " AS F " +
					" WHERE F." + param + " > " + max +
					" ORDER BY F." + param + " ASC ; " ;
		}
		this.out(query) ;
		// RESULT-SET
		Statement mST = null ;
		ResultSet mRS = null ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ;
			while (mRS.next()) {
				next_val = mRS.getDouble(param) ;
			}
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		return next_val ;
	}

	//____________________________________________________________________________________________________
	// GET COUNT // Target Count

	public int fnGetTargetCount(double ratio, String date)
	{
		int targetCount = 0 ;
		// PREPARE
		String t_bd_full_log = "t_bd_full_log_" + date ;
		// QUERY
		String query = "" ;
		query = "SELECT COUNT(*) AS 'cnt' FROM " + t_bd_full_log ;
		this.out(query) ;
		// RESULT-SET
		Statement mST = null ;
		ResultSet mRS = null ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ;
			while (mRS.next()) {
				int totalCount = mRS.getInt("cnt") ;
				targetCount = (int)Math.ceil((double)(totalCount * ratio)/100.0) ;
				System.out.println("\n[TARGET RATIO]\n" + ratio) ;
				System.out.println("\n[TARGET COUNT]\n" + targetCount + " (of " + totalCount + ")") ;
			}
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		return targetCount ;
	}

	//____________________________________________________________________________________________________
	// GET COUNT // Extracted List Count

	public int fnGetCount(emoParam param, emoPattern range, String date)
	{
		int targetCount = 0 ;
		// PREPARE
		String t_bd_full_log = "t_bd_full_log_" + date ;
		// QUERY
		String query = "" ;
		query = "SELECT COUNT(*) AS 'cnt' FROM " + t_bd_full_log + " AS F WHERE "  ;
		for(int i=0; i<param.length(); i++)
		{
			if(i!=0) query += "AND " ;
			query += String.format("(%f <= F.%s AND F.%s <= %f)", range.min(i), param.param(i), param.param(i), range.max(i)) ;
		}
		this.out(query) ;
		// RESULT-SET
		Statement mST = null ;
		ResultSet mRS = null ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ;
			while (mRS.next()) {
				targetCount = mRS.getInt("cnt") ;
			}
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		return targetCount ;
	}

	//____________________________________________________________________________________________________
	// EOC
	
	public boolean log = false ;
	private void out (Object obj)
	{
		if(log) System.out.println(obj) ;
	}
}
